import pymysql


class Orm():
    """mysql handle"""
    def __init__(self, host='127.0.0.1', port=3306, user='root', password='mysql', db='sina'):
        self.conn = pymysql.connect(host=host, port=port, user=user, password=password, db=db,use_unicode=True, charset="utf8")
        self.cur = self.conn.cursor()
    
    def table(self, name, method, data=None, method2='one'):
        """table handle"""
        if method == "put":
            return self.table_put(name, data)
        if method == "get":
            return self.table_get(name, data, method2)
        if method == "update":
            return self.table_update(name, data)
        if method == 'delete':
            return self.table_delete(name, data)

    def table_put(self, name, data):
        """put data"""
        sql1 = str()
        sql2 = str()
        for key in data.keys():
            if type(data[key]) is str:
                data[key] = data[key].replace("'", "\\\'").replace('"', '\\\"')
        for key in data.keys():
            sql1 += """%s, """%str(key) 
            if type(data[key]) is int:
                sql2 += """%s, """%str(data[key])
            else: sql2 += """'%s', """%str(data[key])
        sql1 = sql1[:-2]
        sql2 = sql2[:-2]
        sql = """insert into %(name)s(%(sql1)s) values(%(sql2)s);"""%{'name': name, 'sql1': sql1, 'sql2': sql2}
        # if field is exist an exception is caught
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as e:
            print("insert to %s fail error is %s, sql: %s"%(name, e, sql))

    def table_get(self, name, data, method):
        """get data""" 
        for key in data.keys():
            if type(data[key]) is str:
                data[key] = data[key].replace("'", "\\\'").replace('"', '\\\"')
        if method == 'one':
            sql1 = str()
            sql2 = str()
            for key in data.keys():
                if type(data[key]) is int:
                    sql1 += """ %s=%s and"""%(str(key), data[key])
                else: sql1 += """ %s='%s' and"""%(str(key), data[key])
            sql1 = sql1[:-3]
            sql = """select * from %(name)s where %(sql1)s;"""%{'name': name, 'sql1': sql1}
            self.cur.execute(sql)
            result = self.cur.fetchone()
            self.conn.commit()
        if method == 'all':
            sql1 = str()
            sql2 = str()
            for key in data.keys():
                if type(data[key]) is int:
                    sql1 += """ %s=%s and"""%(str(key), data[key])
                else: sql1 += """ %s='%s' and"""%(str(key), data[key])
            sql1 = sql1[:-3]
            sql = """select * from %(name)s where %(sql1)s;"""%{'name': name, 'sql1': sql1}
            self.cur.execute(sql)
            result = self.cur.fetchall()
            self.conn.commit()
        return result
    
    def table_update(self, name, data):
        """update data"""
        condition = data[0]
        value = data[1]
        for key in value.keys():
            if type(value[key]) is str:
                value[key] = value[key].replace("'", "\\\'").replace('"', '\\\"')
        for key in condition.keys():
            if type(condition[key]) is str:
                condition[key] = condition[key].replace("'", "\\\'").replace('"', '\\\"')
        sql1 = str()
        sql2 = str()
        for key in value:
            if type(value[key]) is int:
                sql2 += "%s=%s, "%(str(key), value[key])
            else: sql2 += """%s='%s', """%(str(key), value[key])
        sql2 = sql2[:-2]
        for key in condition:
            if type(condition[key]) is int:
                sql1 += """ %s=%s and"""%(str(key), condition[key])
            else: sql1 += """ %s='%s' and"""%(str(key), condition[key])
        sql1 = sql1[:-3]
        sql = """update %(name)s set %(sql2)s where %(sql1)s;"""%{'name': name, 'sql1': sql1, 'sql2': sql2}
        self.cur.execute(sql)
        self.conn.commit()
    
    def table_delete(self, name, data):
        """delete data"""
        sql1 = str()
        for key in data.keys():
            if type(data[key]) is str:
                data[key] = data[key].replace("'", "\\\'").replace('"', '\\\"')
        for key in data.keys():
            if type(data[key]) is int:
                sql1 += """ %s=%s and"""%(str(key), data[key])
            else: sql1 += """ %s='%s' and"""%(str(key), data[key])
        sql1 = sql1[:-3]
        sql = """delete from %(name)s where %(sql1)s;"""%{'name': name, 'sql1': sql1}
        self.cur.execute(sql)
        self.conn.commit()
    
    def sql(self, sql, method='get'):
        if method == "get":
            self.cur.execute(sql)
            result = self.cur.fetchall()
            self.conn.commit()
            return result
        else:
            self.cur.execute(sql)
            self.conn.commit()

    def close(self):
        self.cur.close()
        self.conn.close()
